Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Database Change Notification Support

Oracle Data Provider for .NET provides a notification framework that supports Database Change Notification, enabling applications to receive notifications when there is a change in a query result set, schema objects, or the state of the database. Using Database Change Notification, an application can maintain the validity of the client-side cache (for example, the ADO.NET `DataSet`) easily.

Note: Database Change Notification is not supported in a .NET stored procedure.

Using the notification framework, applications can specify a query result set as a registered query for notification request, and create this notification registration to maintain the validity of the query result set. When there is a change in the query result set, the notification framework notifies the application.

Note: The content of a change notification is referred to as an invalidation message. It indicates that the query result set is now invalid and provides information about the changes.

Based on the information provided by the notification framework, the application can then act accordingly. For example, the application might need to refresh its own copy of the data for the registered query that is stored locally in the application.

The database notifies Oracle Data Provider for .NET of data changes made to the underlying tables when this data is being used on a .NET client.

Note: If a registered object is dropped from the database and a new one is created with the same name in the same schema, re-registration is required to receive notifications for the newly created object.

See Also: Oracle Database Application Developer's Guide - Fundamentals for further information on Database Change Notification

This section contains the following topics:

- Database Change Notification Classes

- Supported Operations

- Requirements of Notification Registration

- Using Database Change Notification

- Best Practice Guidelines and Performance Considerations

## Database Change Notification Classes

The following classes are associated with the Database Change Notification Support:

- `OracleDependency`

  Represents a dependency between an application and an Oracle database based on the database events which the application is interested in. It contains information about the dependency and provides the mechanism to notify the application when specified database events occurs. The `OracleDependency` class is also responsible for creating the notification listener to listen for database notifications. There is only one database notification listener for each application domain. This notification listener terminates when the application process terminates.

  The dependency between the application and the database is not established when the `OracleDependency` object is created. The dependency is established when the command that is associated with this `OracleDependency` object is executed. That command execution creates a database change notification registration in the database.

  When a change has occurred in the database, the `HasChanges` property of the `OracleDependency` object is set to true. Furthermore, if an event handler was registered with the `OnChange` event of the `OracleDependency` object, the registered event handler function will be invoked.

- `OracleNotificationRequest`

  Represents a notification request to be registered in the database. It contains information about the request and the properties of the notification.

- `OracleNotificationEventArgs`

  Represents the invalidation message generated for a notification when a specified database event occurs and contains details about that database event.

  See Also: "OracleDependency Class" "OracleNotificationRequest Class" "OracleNotificationEventArgs Class"

## Supported Operations

The ODP.NET notification framework in conjunction with Database Change Notification supports the following activities:

- Creating a notification registration by:

  - Creating an `OracleDependency` instance and binding it to an `OracleCommand` instance.

- Grouping multiple notification requests into one registration by:

  - Using the `OracleDependency.AddCommandDependency` method.

  - Setting the `OracleCommand.Notification` request using the same `OracleNotificationRequest` instance.

- Registering for database change notification by:

  - Executing the `OracleCommand`. If either the notification property is null or `NotificationAutoEnlist` is false, the notification will not be made.

- Removing notification registration by:

  - Using the `OracleDependency.RemoveRegistration` method.

  - Setting the `Timeout` property in the `OracleNotificationRequest` instance before the registration is created.

  - Setting the `IsNotifiedOnce` property to `true` in the `OracleNotificationRequest` instance before the registration is created. The registration is removed once a database notification is sent.

- Ensuring Change Notification Persistence by:

  - Specifying whether or not the invalidation message is queued persistently in the database before delivery. If an invalidation message is to be stored persistently in the database, then the change notification is guaranteed to be sent. If an invalidation message is stored in an in-memory queue, the change notification can be received faster, however, it could be lost upon database shutdown or crashes.

- Retrieving notification information including:

  - The changed object name.

  - The schema name of the changed object.

  - Database events that cause the notification, such as insert, delete, and so on.

  - The `RowID` of the modified object row.

- Defining the listener port number.

  By default, the static `OracleDependency.Port` property is set to `-1`. This indicates that the ODP.NET listens on a port that is randomly picked when ODP.NET registers a database change notification request for the first time during the execution of an application.

  ODP.NET creates only one listener that listens on one port within an application domain. Once ODP.NET starts the listener, the port number cannot be changed; Changes to the static `OracleDependency.Port` property are ignored.

  By default, all installations of Windows XP Service Pack 2 and higher enable the Windows Firewall to block virtually all TCP network ports to incoming connections. Therefore, for Database Change Notification to work properly on Windows XP Service Pack 2 and higher, the Windows Firewall must be configured properly to allow specific executables to open specific ports.

See Also: Oracle Database Platform Guide for Windows for details on configuring the Windows Firewall "OracleCommand Class" "Notification" "NotificationAutoEnlist" "OracleDependency Class"

## Requirements of Notification Registration

The connected user must have the `CHANGE` `NOTIFICATION` privilege to create a notification registration.

This SQL statement grants the `CHANGE` `NOTIFICATION` privilege:

    grant change notification to user name

This SQL statement revokes the `CHANGE` `NOTIFICATION` privilege:

    revoke change notification from user name

## Using Database Change Notification

This section describes what the application should do, and the flow of the process, when an application uses Database Change Notification to receive notifications for any changes in the registered query result set.

### Application Steps

The application should do the following:

1. Create an `OracleDependency` instance.

2. Assign an event handler to the `OracleDependency.OnChange` event property if the application wishes to have an event handler invoked when database changes are detected. Otherwise, the application can choose to poll on the `HasChanges` property of the `OracleDependency` object. This event handler is invoked when the change notification is received.

3. Set the port number for the listener to listen on. The application can specify the port number for one notification listener to listen on. If the application does not specify a port number, a random one is used by the listener.

4. Bind the `OracleDependency` instance to an `OracleCommand` instance that contains the actual query to be executed. Internally, the database change notification request (an `OracleNotificationRequest` instance) is created and assigned to the `OracleCommand.Notification` property.

### Flow of Notification Process

1. When the command associated with the notification request is executed, the notification registration is created in the database. The command execution must return a result set, or contain one or more `REF` cursors for a PL/SQL stored procedure.

2. ODP.NET starts the application listener on the first successful notification registration.

3. When a change related to the registration occurs in the database, the application is notified through the event delegate assigned to the `OracleDependency.OnChange` event property, or the application can poll the `OracleDependency.HasChanges` property.

The following example demonstrates the database change notification feature.

    // Database Setup
    // NOTE: unless the following SQL command is executed,
    // ORA-29972 will be obtained from running this sample
    /*
    grant change notification to scott;
    */
    using System;
    using System.Threading;
    using System.Data;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;

    //This sample shows the database change notification feature in ODP.NET.
    //Application specifies to get a notification when emp table is updated.
    //When emp table is updated, the application will get a notification
    //through an event handler.
    namespace NotificationSample
    {
      public class MyNotificationSample
      {
        public static bool IsNotified = false;

        public static void Main(string[] args)
        {
          //To Run this sample, make sure that the change notification privilege
          //is granted to scott.
          string constr = "User Id=scott;Password=tiger;Data Source=oracle";
          OracleConnection con = null;
          OracleDependency dep = null;

          try
          {
            con = new OracleConnection(constr);
            OracleCommand cmd = new OracleCommand("select * from emp", con);
            con.Open();

            // Set the port number for the listener to listen for the notification
            // request
            OracleDependency.Port = 1005;

            // Create an OracleDependency instance and bind it to an OracleCommand
            // instance.
            // When an OracleDependency instance is bound to an OracleCommand
            // instance, an OracleNotificationRequest is created and is set in the
            // OracleCommand's Notification property. This indicates subsequent
            // execution of command will register the notification.
            // By default, the notification request is using the Database Change
            // Notification.
            dep = new OracleDependency(cmd);

            // Add the event handler to handle the notification. The
            // OnMyNotification method will be invoked when a notification message
            // is received from the database
            dep.OnChange +=
              new OnChangeEventHandler(MyNotificationSample.OnMyNotificaton);

            // The notification registration is created and the query result sets
            // associated with the command can be invalidated when there is a
            // change.  When the first notification registration occurs, the
            // notification listener is started and the listener port number
            // will be 1005.
            cmd.ExecuteNonQuery();

            // Updating emp table so that a notification can be received when
            // the emp table is updated.
            // Start a transaction to update emp table
            OracleTransaction txn = con.BeginTransaction();
            // Create a new command which will update emp table
            string updateCmdText =
              "update emp set sal = sal + 10 where empno = 7782";
            OracleCommand updateCmd = new OracleCommand(updateCmdText, con);
            // Update the emp table
            updateCmd.ExecuteNonQuery();
            //When the transaction is committed, a notification will be sent from
            //the database
            txn.Commit();
          }
          catch (Exception e)
          {
            Console.WriteLine(e.Message);
          }

          con.Close();
          // Loop while waiting for notification
          while(MyNotificationSample.IsNotified == false)
          {
            Thread.Sleep(100);
          }
        }

        public static void OnMyNotificaton(object src,
          OnChangeEventArgs arg)
        {
          Console.WriteLine("Notification Received");
          DataTable changeDetails = arg.Details;
          Console.WriteLine("Data has changed in {0}",
            changeDetails.Rows[0]["ResourceName"]);
          MyNotificationSample.IsNotified = true;
        }
      }
    }

## Best Practice Guidelines and Performance Considerations

This section provides guidelines for working with Database Change Notification and the ODP.NET notification framework, and discusses the performance impacts.Every change notification registration consumes database memory, storage or network resources, or some combination thereof. The resource consumption further depends on the volume and size of the invalidation message. In order to scale well with a large number of mid-tier clients, Oracle recommends that the client implement these best practices:

- Few and mostly read-only tables

  There should be few registered objects, and these should be mostly read-only, with very infrequent invalidations. If an object is extremely volatile, then a large number of invalidation notifications are sent, potentially requiring a lot of space (in memory or on disk) in the invalidation queue. This is also true if a large number of objects are registered.

- Few rows updated for each table

  Transactions should update (or insert or delete) only a small number of rows within the registered tables. Depending on database resources, a whole table could be invalidated if too many rows are updated within a single transaction, for a given table.

  This policy helps to contain the size of a single invalidation message, and reduces disk storage for the invalidation queue.

See Also: Oracle Database Application Developer's Guide - Fundamentalsfor further information on Database Change Notification